/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package worker;

import book.PhoneBookItem;
import db.MySQL;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Random;

/**
 *
 * @author x3m
 */
public class Worker_db implements IPhoneWorker {

    @Override
    public void generateUsers(int count) {
        //INSERT INTO `tPhoneBook` (`cLogin`, `cPass`, `cPrivateID`, `cSurname`, `cFirstName`, `cSecondName`, `cPhone1`, `cAddr`, `cRegDate`, `cAvatar`) VALUES ('RND_Login', 'RND_Pass', 'user_RND', 'RND_surname', 'RND_fname', 'RND_lname', '050-000-00-00', 'Мирный 1/1', '2011-11-13 19:57:24', NULL);
        try {
            MySQL db = new MySQL("coderz.mysql.ukraine.com.ua", "coderz_req", "coderz_req", "flatron");
            db.Connect();

            for (int i = 0; i < count; i++) {
                Random rnd = new Random();
                int val = rnd.nextInt(1000000);
                db.queryUpdate("INSERT INTO `tPhoneBook` (`cLogin`, `cPass`, `cPrivateID`, `cSurname`, `cFirstName`, `cSecondName`, `cPhone1`, `cAddr`, `cRegDate`, `cAvatar`) VALUES ('Логин_" + val + "', 'Пароль_" + val + "', 'user_" + val + "', 'Фамилия_" + val + "', 'Имя_" + val + "', 'Отчество_" + val + "', '050-000-00-00', 'Мирный 1/1', '2011-11-13 00:00:00', NULL)");
            }
            db.Close();

        } catch (Exception e) {
        }
    }

    @Override
    public PhoneBookItem[] getItems() {
        ArrayList<PhoneBookItem> items = new ArrayList();
        try {
            //MySQL db = new MySQL("127.0.0.1","web", "root", "root");
            MySQL db = new MySQL("coderz.mysql.ukraine.com.ua", "coderz_req", "coderz_req", "flatron");

            db.Connect();
            ResultSet rs = db.querySelect("SELECT * FROM tPhoneBook");

            while (rs.next()) {
                //чтение из бд записей
                PhoneBookItem item = new PhoneBookItem();
                item.setSurname(rs.getString("cSurname"));
                item.setFirst_name(rs.getString("cFirstName"));
                item.setSecond_name(rs.getString("cSecondName"));
                item.setPhone1(rs.getString("cPhone1"));
                item.setPhone2(rs.getString("cPhone2"));
                item.setPhone3(rs.getString("cPhone3"));
                item.setPrivateID(rs.getString("cPrivateID"));
                item.setAddr(rs.getString("cAddr"));
                // добавляем запись
                items.add(item);
            }
            db.Close();

        } catch (SQLException ex) {
        }

        return items.toArray(new PhoneBookItem[items.size()]);
    }

    @Override
    public PhoneBookItem getItem(String id) {
        //SELECT `cID`, `cLogin`, `cPass`, `cPrivateID`, `cSurname`, `cFirstName`, `cSecondName`, `cPhone1`, `cPhone2`, `cPhone3`, `cAddr`, `cRegDate`, `cLastUpdate`  FROM `coderz_req`.`tPhoneBook`
        try {
            MySQL db = new MySQL("coderz.mysql.ukraine.com.ua", "coderz_req", "coderz_req", "flatron");
            db.Connect();

            ResultSet rs = db.querySelect("SELECT `cID`, `cPrivateID`, `cSurname`, `cFirstName`, `cSecondName`, `cPhone1`, `cPhone2`, `cPhone3`, `cAddr`   FROM `coderz_req`.`tPhoneBook` WHERE `cPrivateID` ='" + id + "'");

            PhoneBookItem item = new PhoneBookItem();
            while (rs.next()) {
                item.setSurname(rs.getString("cSurname"));
                item.setFirst_name(rs.getString("cFirstName"));
                item.setSecond_name(rs.getString("cSecondName"));
                item.setPhone1(rs.getString("cPhone1"));
                item.setPhone2(rs.getString("cPhone2"));
                item.setPhone3(rs.getString("cPhone3"));
                item.setPrivateID(rs.getString("cPrivateID"));
                item.setAddr(rs.getString("cAddr"));
            }
            db.Close();
            return item;

        } catch (Exception e) {
        }
        return null;
    }

    @Override
    public Boolean addItem(PhoneBookItem item) {
        //throw new UnsupportedOperationException("Not supported yet.");
        //INSERT INTO `tPhoneBook` (`cLogin`, `cPass`, `cPrivateID`, `cSurname`, `cFirstName`, `cSecondName`, `cPhone1`, `cPhone2`, `cPhone3`, `cAddr`, `cRegDate`, `cAvatar`) 
        //VALUES ('11', 'RND_Pass_-1462561899', '33', 'a', 'b', 'c', '1', '2', '3', 'Мирный 1/11', '2011-11-13 00:00:00', NULL);
        try {
            MySQL db = new MySQL("coderz.mysql.ukraine.com.ua", "coderz_req", "coderz_req", "flatron");
            Random rnd = new Random();
            db.Connect();
            int res = db.queryUpdate("INSERT INTO `tPhoneBook` (`cLogin`, `cPass`, `cPrivateID`, `cSurname`, `cFirstName`, `cSecondName`, `cPhone1`, `cPhone2`, `cPhone3`, `cAddr`) VALUES ('login_"+ rnd.nextInt(1000000) +"', '-', 'user_" + rnd.nextInt(1000000) + "', '"+item.getSurname()+"', '"+item.getFirst_name()+"', '"+item.getSecond_name()+"', '"+item.getPhone1()+"', '"+item.getPhone2()+"', '"+item.getPhone3()+"', '"+item.getAddr()+"')");
            db.Close();
            if(res > 0)
            {
                return true;
            }else{
                return false;
            }            
        } catch (Exception e) {
            return false;
        }        
    }

    @Override
    public void deleteItem(String id) {

        try {
            MySQL db = new MySQL("coderz.mysql.ukraine.com.ua", "coderz_req", "coderz_req", "flatron");
            db.Connect();
            int res = db.queryUpdate("DELETE FROM `tPhoneBook` WHERE `cPrivateID`='" + id + "'");
            db.Close();

        } catch (Exception e) {
        }
        //DELETE FROM `tPhoneBook` WHERE `cID`=3 LIMIT 1;
    }

    @Override
    public Boolean editItem(PhoneBookItem item) {
        //throw new UnsupportedOperationException("Not supported yet.");
        //UPDATE `tPhoneBook` SET `cSurname`='a', `cFirstName`='b', `cSecondName`='c', `cPhone1`='1', 
        //`cPhone2`='2', `cPhone3`='3', `cAddr`='Мирный 1/11' WHERE `cID`=56 LIMIT 1;
        try {
            MySQL db = new MySQL("coderz.mysql.ukraine.com.ua", "coderz_req", "coderz_req", "flatron");
            db.Connect();

            int res = db.queryUpdate("UPDATE `tPhoneBook` SET `cSurname`='" + item.getSurname() + "', `cFirstName`='" + item.getFirst_name() + "', `cSecondName`='" + item.getSecond_name() + "', `cPhone1`='" + item.getPhone1() + "', `cPhone2`='" + item.getPhone2() + "', `cPhone3`='" + item.getPhone3() + "', `cAddr`='" + item.getAddr() + "' WHERE `cPrivateID` = '" + item.getPrivateID() + "'");
            
            db.Close();
            
            if(res > 0)
            {
                return true;
            }else{
                return false;
            }

        } catch (Exception e) {
            return false;
        }
    }
}
